﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Reflection;
using Microsoft.Data.SqlClient;
using BK_SMTDataUpload;
using BK_SMTDataUpload.model;
using NPOI.HSSF.Record.PivotTable;

class ReadWriteDatabase
{
    SqlConnection connection;
    private string connectionString = "Data Source=192.168.5.97;Initial Catalog=BK_SMTDeviceData;User ID=sa;Password=gl.2008;Encrypt=false;TrustServerCertificate=true;";
    public ReadWriteDatabase(ref string exception)
    {
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
        }
        catch (Exception ex)
        {
            exception = ex.ToString();
            return;
        }
    }

    public async Task insertData(object obj, string tabName)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();
        List<string> lsit_colNames = new List<string>();
        List<string> list_parameterNames = new List<string>();
        List<string> list_Value = new List<string>();
        foreach (var item in properties)
        {
            lsit_colNames.Add(item.Name);
            list_parameterNames.Add($"@{item.Name}");
            list_Value.Add(item.GetValue(obj).ToString());
        }
        string colNames = string.Join(",", lsit_colNames);
        string parameterName = string.Join(",", list_parameterNames);
        string insertString = $"insert into {tabName} ({colNames}) values ({parameterName})";
        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
        {
            await sqlConnect.OpenAsync();
            using (SqlCommand command = new SqlCommand(insertString, sqlConnect))
            {
                for (int i = 0; i < list_parameterNames.Count; i++)
                {
                    command.Parameters.AddWithValue(list_parameterNames[i], list_Value[i]);
                }
                await command.ExecuteNonQueryAsync();
            }
        }
    }
    
    public async Task InsertJukiData(JUKI_ProductionRecords juki)
    {
        string sqlSelects = $"SELECT * FROM juki_ProduceRecords WHERE MachineName ='{juki.MachineName}'AND model='{juki.model}' AND CAST(Time AS DATE) =CAST('{DateTime.Now.Date}' AS DATE)";
        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
        {
            await sqlConnect.OpenAsync();
            using (SqlCommand sqlcd = new SqlCommand(sqlSelects, sqlConnect))
            {
                using (SqlDataReader sr =await sqlcd.ExecuteReaderAsync())
                {
                    if (sr.HasRows)
                    {
                        while (sr.Read())
                        {
                            await jukiUpdate(juki);
                            return;
                        }
                    }
                }
            }
        }
        await insertData(juki , "juki_ProduceRecords");
    }

    public async Task<int> InsertSMData(SM_ProductionRecords SM,string TabName)
    {
        try
        {
            int Number = 0;
            string sqlSelects = $"SELECT * FROM {TabName} WHERE MachineName ='{SM.MachineName}'AND ProductName='{SM.ProductName}' AND CAST(Utime AS DATE) =CAST('{DateTime.Now.Date}' AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand sqlcd = new SqlCommand(sqlSelects, sqlConnection))
                {
                    using (SqlDataReader sr = sqlcd.ExecuteReader())
                    {
                        if (sr.HasRows)
                        {
                            while (sr.Read())
                            {
                                SMUpdate(SM);
                                return 0;
                            }
                        }
                    }
                }
            }
            await insertData(SM, TabName);
            return Number = 1;
        }
        catch (Exception ex)
        {
            FileTool.ErrorRecord(ex.ToString() + "           SM", MainForm.Errorlog);
            return -1;
        }
       
    }

    public async Task InsertDatabase(object obj, string tableName)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();
        List<string> columnNames = new List<string>();
        List<string> parameterNames = new List<string>();
        List<string> value = new List<string>();
        foreach (PropertyInfo property in properties)
        {
            if (property.Name == "id") continue;
            string columnName = property.Name;
            string parameterName = $"@{columnName}";
            columnNames.Add(columnName);
            parameterNames.Add(parameterName);
            object propertyValue = property.GetValue(obj);
            if (propertyValue != null)
            {
                value.Add(propertyValue.ToString());
            }
            else
            {
                value.Add("");
            }
        }
        string columns = string.Join(", ", columnNames);
        string parameters = string.Join(", ", parameterNames);
        string insertQuery = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            await sqlConnection.OpenAsync();
            using (SqlCommand sqlcd = new SqlCommand(insertQuery, sqlConnection))
            {
                for (int i = 0; i < parameterNames.Count; i++)
                {
                    sqlcd.Parameters.AddWithValue(parameterNames[i], value[i]);
                }
                sqlcd.ExecuteNonQuery();
            }
        }

    }

    public async Task<string> selectData(string tabName, string where1, string FName, string where2, string MNane)
    {
        try
        {
            string str_Selects = $"select * from {tabName} where {where1} ='{FName}'and {where2} ='{MNane}'";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Selects, sqlConnection))
                {
                    using (SqlDataReader sr = command.ExecuteReader())
                    {
                        if (sr.HasRows)
                        {
                            while (sr.Read())
                            {
                                return sr[where1].ToString();
                            }
                        }
                    }
                }
            }
            return "";
        }
        catch (Exception ex)
        {
            FileTool.ErrorRecord(ex.ToString() + "           1", MainForm.Errorlog);
            return "1";
        }

    }

    public async Task jukiUpdate(JUKI_ProductionRecords juki)
    {
        try
        {
            Type type = juki.GetType();
            PropertyInfo[] properties = type.GetProperties();
            List<string> columnNames = new List<string>();
            List<string> value = new List<string>();
            foreach (PropertyInfo property in properties)
            {
                string columnName = property.Name;
                string str_select = $"select * from juki_ProduceRecords where MachineName='{juki.MachineName}' and  model='{juki.model}' and CAST(Time as date)=CAST(GETDATE() as date)";
                string str_update = "";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(str_select, sqlConnection))
                    {
                        using (SqlDataReader sr = await command.ExecuteReaderAsync())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    str_update = $"update juki_ProduceRecords set {columnName}='{property.GetValue(juki)}' where id='{sr["id"]}'";
                                }
                            }
                        }
                    }
                }
                if (str_update != "")
                {
                    using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                    {
                        await sqlConnect.OpenAsync();
                        using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                        {
                            await sql.ExecuteNonQueryAsync();
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {

        }



    }

    public async Task jukiUpdate(ATE_ProductionRecords ATE)
    {
        try
        {
            Type type = ATE.GetType();
            PropertyInfo[] properties = type.GetProperties();
            List<string> columnNames = new List<string>();
            List<string> value = new List<string>();
            foreach (PropertyInfo property in properties)
            {
                string columnName = property.Name;
                string str_select = $"select * from ATE_ProduceRecords where MachineName='{ATE.MachineName}' and  NroductName='{ATE.NroductName}' and CAST(TestTime as date)=CAST(GETDATE() as date)";
                string str_update = "";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(str_select, sqlConnection))
                    {
                        using (SqlDataReader sr = await command.ExecuteReaderAsync())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    str_update = $"update ATE_ProduceRecords set {columnName}='{property.GetValue(ATE)}' where id='{sr["id"]}'";
                                }
                            }
                        }
                    }
                }
                if (str_update != "")
                {
                    using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                    {
                        await sqlConnect.OpenAsync();
                        using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                        {
                            await sql.ExecuteNonQueryAsync();
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {

        }



    }

    public void SMUpdate(SM_ProductionRecords SM)
    {
        Type type = SM.GetType();
        PropertyInfo[] properties = type.GetProperties();
        List<string> columnNames = new List<string>();
        List<string> value = new List<string>();
        foreach (PropertyInfo property in properties)
        {
            string columnName = property.Name;
            string str_select = $"select * from sm_ProduceRecords where MachineName='{SM.MachineName}' and  ProductName='{SM.ProductName}' and CAST(Utime as date)=CAST(GETDATE() as date)";
            string str_update = "";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = new SqlCommand(str_select, sqlConnection))
                {
                    using (SqlDataReader sr = command.ExecuteReader())
                    {
                        if (sr.HasRows)
                        {
                            while (sr.Read())
                            {
                                str_update = $"update sm_ProduceRecords set {columnName}='{property.GetValue(SM)}' where id='{sr["id"]}'";
                            }
                        }
                    }
                }
            }
            if (str_update != "")
            {
                using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                {
                    sqlConnect.Open();
                    using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                    {
                        sql.ExecuteNonQuery();
                    }
                }
            }
        }
    }

    public async Task DeleteDate(string TabName, string timeColName)
    {
        string delete = $"delete from {TabName} where CAST({timeColName} AS DATE) < CAST(DATEADD(DAY, -5, GETDATE()) AS DATE)";
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            await sqlConnection.OpenAsync();
            using (SqlCommand command = new SqlCommand(delete, sqlConnection))
            {
                await command.ExecuteNonQueryAsync();
            }
        }
    }

    public async Task<bool> SelectData(string query)
    {
        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
        {
            try
            {
                await sqlConnect.OpenAsync();
            }
            catch (Exception)
            {
                try
                {
                    await Task.Delay(2000);
                    await sqlConnect.OpenAsync();
                }
                catch (Exception ex)
                {
                    FileTool.ErrorRecord(ex.ToString() + DateTime.Now.ToString(), MainForm.Errorlog);
                    return false;
                }
            }
            using (SqlCommand command = new SqlCommand(query, sqlConnect))
            {
                using (SqlDataReader sr =await command.ExecuteReaderAsync())
                {
                    if (sr.HasRows)
                    {
                        while (sr.Read())
                        {
                            return true;
                        }
                    }
                }
            }
        }
       
        return false;
    }

    public async Task UpdateDate(object obj, string query, string table)
    {
        try
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties();
            List<string> columnNames = new List<string>();
            List<string> value = new List<string>();
            foreach (PropertyInfo property in properties)
            {
                string columnName = property.Name;
                string str_update = "";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(query, sqlConnection))
                    {
                        using (SqlDataReader sr = await command.ExecuteReaderAsync())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    str_update = $"update {table} set {columnName}='{property.GetValue(obj)}' where id='{sr["id"]}'";
                                }
                            }
                        }
                    }
                }
                if (str_update != "")
                {
                    using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                    {
                        await sqlConnect.OpenAsync();
                        using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                        {
                            await sql.ExecuteNonQueryAsync();
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            return;
        }



    }

    public async Task<List<T>> QueryDataAsync<T>(string selectString) where T : new()
    {
        var result = new List<T>();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();
            using (SqlCommand command = new SqlCommand(selectString, connection))
            {
                using (SqlDataReader dr = await command.ExecuteReaderAsync())
                {
                    var properties = typeof(T).GetProperties().ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase);
                    while (dr.Read())
                    {
                        var item = new T();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            var columnName = dr.GetName(i);
                            if (properties.TryGetValue(columnName, out var property) && !dr.IsDBNull(i))
                            {
                                var value = dr.GetValue(i);
                                var convertedValue = Convert.ChangeType(value, property.PropertyType);
                                property.SetValue(item, convertedValue);
                            }
                        }
                        result.Add(item);
                    }
                }
            }
        }
        return result;
    }

    public async Task<int> SelectDataAsync(string TabName, string colName)
    {
        try
        {
            List<string> list = new List<string>();
            string str_Select = $"SELECT DISTINCT MachineName FROM {TabName} where CAST({colName} AS DATE)=CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    SqlDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        list.Add(dr["MachineName"].ToString());
                    }
                }
            }
            return list.Count;
        }
        catch (Exception)
        {
            return 0;
        }
    }

    public async Task<List<string>> OnlineMachine(string TabName, string colName)
    {
        List<string> list = new List<string>();
        string str_Select = $"SELECT DISTINCT MachineName FROM {TabName} where CAST({colName} AS DATE)=CAST(GETDATE() AS DATE)";
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            await sqlConnection.OpenAsync();
            using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    list.Add(dr["MachineName"].ToString());
                }
            }
        }
        return list;
    }

    public async Task<int> SelectMachineNamesync(string query)
    {
        List<string> list = new List<string>();
        try
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(query, sqlConnection))
                {
                    SqlDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        string machineName = dr["MachineName"].ToString();
                        if (!list.Contains(machineName))
                        {
                            list.Add(machineName);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            return 0;
        }
        return list.Count;
    }


}
